package com.css.fxfzdzzh.modules.reportManagement.repository;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.modules.reportManagement.entity.BreakReport;
import com.css.fxfzdzzh.modules.reportManagement.entity.HistoryReport;
import com.css.fxfzdzzh.modules.reportManagement.entity.Report;
import com.css.fxfzdzzh.modules.reportManagement.param.PageQueryParam;
import com.css.fxfzdzzh.modules.surfaceRupture.entity.SurfaceRupture;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年05月09日  15:20
 */
@Repository
public class ReportRepository {

    @Resource
    SUserService sUserService;
    @Resource
    SysRoleService sysRoleService;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    JdbcTemplate jdbcTemplate;
    public Map<String, Object> breakReportPage(PageQueryParam pageQueryParam, int curPage, int pageSize) {
        StringBuilder sql =new StringBuilder(
            "select distinct(rupture.num),rupture.evaluate_user_name,"
                + "CASE WHEN report.check_status = '0' THEN '0'"
                + " WHEN report.check_status is null THEN '0' "
                + " WHEN report.check_status='1' THEN '1'"
                + " WHEN report.check_status='2' THEN '2'"
                + " else '3' end as check_status,"
                + "concat_ws('',province,city,county) as district,rupture.report_id,report.upload_break_report_name,report.system_create_break_report_name  from dzzh_surface_rupture rupture LEFT JOIN dzzh_task_break_report report ON rupture.report_id = report.dtbr_id ");
        StringBuilder whereSql = new StringBuilder(" where rupture.del_flag = '0' ");
        StringBuilder countSql = new StringBuilder("select count(distinct(rupture.num)) from dzzh_surface_rupture rupture LEFT JOIN dzzh_task_break_report report ON rupture.report_id = report.dtbr_id  ");
        int firstIndex = (curPage- 1) * pageSize;
        int lastIndex = pageQueryParam.getRows();
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        // 本人评估的结果，本人才能查看和删除; 系统生成的结果，只有国家级管理员可以查看和删除
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains(FxfzConstants.DZZH_COUN_YW_ROLE)) {
            //查看全部
        }else if(roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE)){
            //省管理员查看自己省的
            whereSql.append(" and rupture.province = '").append(pageQueryParam.getProvince()).append("' ");
        } else {
            //查看自己的
            whereSql.append(" and rupture.evaluate_user = '").append(userId).append("' ");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getProvince())) {
            whereSql.append(" and rupture.province = '").append(pageQueryParam.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getCity())) {
            whereSql.append(" and rupture.city = '").append(pageQueryParam.getCity()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getArea())) {
            whereSql.append(" and rupture.county = '").append(pageQueryParam.getArea()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getCheckStatus())) {
            if("0".equals(pageQueryParam.getCheckStatus())){
                whereSql.append(" and (report.check_status = '").append(pageQueryParam.getCheckStatus()).append("' or report.check_status is null)");
            }else{
                whereSql.append(" and report.check_status = '").append(pageQueryParam.getCheckStatus()).append("'");
            }

        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getNum())) {
            whereSql.append(" and rupture.num like '%").append(pageQueryParam.getNum()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(pageQueryParam.getEvaluateUserName())) {
            whereSql.append(" and rupture.evaluate_user_name like '%").append(pageQueryParam.getEvaluateUserName()).append("%'");
        }

        String sqlWhole = sql.append(whereSql).append("order by num limit ").append(pageSize).append(" offset ").append(firstIndex).toString();
        countSql.append(whereSql);
        List<BreakReport> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(BreakReport.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    public BreakReport getReportById(String reportId) {
        String sql = "select * from dzzh_task_break_report where dtbr_id='"+reportId+"'";
        List<BreakReport> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BreakReport.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }

    public void save(BreakReport breakReport) {
    String sql =
        "insert into dzzh_task_break_report(dtbr_id,"
            + "system_create_break_report_name,"
            + "disk_system_create_break_report_name,"
            + "system_break_create_time,"
            + "system_break_create_user_id,"
            + "upload_break_report_name,"
            + "disk_upload_break_report_name,"
            + "upload_break_report_time,"
            + "upload_break_user_id,"
            + "report_save_path,"
            + "check_status,num,district) values(:dtbrId, :systemCreateBreakReportName, :diskSystemCreateBreakReportName, :systemBreakCreateTime, :systemBreakCreateUserId, :uploadBreakReportName, :diskUploadBreakReportName , "
            + ":uploadBreakReportTime, :uploadBreakUserId, :reportSavePath, :checkStatus,:num,:district) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(breakReport);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public void updateUploadReport(BreakReport breakReport) {
        StringBuilder sql = new StringBuilder("update dzzh_task_break_report set ");
        if(PlatformObjectUtils.isNotEmpty(breakReport.getUploadBreakReportName())){
            sql.append("upload_break_report_name='").append(breakReport.getUploadBreakReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(breakReport.getDiskUploadBreakReportName())){
            sql.append("disk_upload_break_report_name='").append(breakReport.getDiskUploadBreakReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(breakReport.getUploadBreakUserId())){
            sql.append("upload_break_user_id='").append(breakReport.getUploadBreakUserId()).append("',");
        }
        sql.append("upload_break_report_time=now()");
        sql.append( "where dtbr_id='").append(breakReport.getDtbrId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void updateSystemCreateReport(BreakReport breakReport) {
        StringBuilder sql = new StringBuilder("update dzzh_task_break_report set ");
        if(PlatformObjectUtils.isNotEmpty(breakReport.getSystemCreateBreakReportName())){
            sql.append("system_create_break_report_name='").append(breakReport.getSystemCreateBreakReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(breakReport.getDiskSystemCreateBreakReportName())){
            sql.append("disk_system_create_break_report_name='").append(breakReport.getDiskSystemCreateBreakReportName()).append("',");
        }
        if(PlatformObjectUtils.isNotEmpty(breakReport.getSystemBreakCreateUserId())){
            sql.append("system_break_create_user_id='").append(breakReport.getSystemBreakCreateUserId()).append("',");
        }
        sql.append("system_break_create_time=now()");
        sql.append( " where dtbr_id='").append(breakReport.getDtbrId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void saveHistoryReport(HistoryReport historyReport) {
        String sql =
                "insert into dzzh_task_history_report(dthr_id,"
                        + "report_name,"
                        + "upload_user_id,"
                        + "upload_user_name,"
                        + "upload_time,"
                        + "num,"
                        + "report_type,"
                        + "disk_report_name,"
                        + "file_save_path) values(:dthrId, :reportName, :uploadUserId, :uploadUserName, :uploadTime, :num, :reportType , :diskReportName,"
                        + ":fileSavePath) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(historyReport);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public Map<String, Object> getHistoryReportByTaskId(String num,String reportType, int curPage, int pageSize) {
        String sql = "select * from dzzh_task_history_report  where num='"+num+"' and report_type='"+reportType+"' order by upload_time desc";
        int firstIndex=(curPage-1) * pageSize;
        String limitSql = " limit "+ pageSize +" offset "+ firstIndex;
        List<HistoryReport> list = jdbcTemplate.query(sql+limitSql,new BeanPropertyRowMapper<>(HistoryReport.class));
        String totalSql = "select count(*) from dzzh_task_history_report  where num='"+num+"' and report_type='"+reportType+"'";
        Integer total = jdbcTemplate.queryForObject(totalSql, Integer.class);
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",list);
        return map;
    }

    public HistoryReport getHistoryReportById(String dthrId) {
        String sql = "select * from dzzh_task_history_report where dthr_id='"+dthrId+"'";
        List<HistoryReport> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HistoryReport.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }

    public void submitCheak(String reportId,String submitUserId,String submitUserName) {
        String sql = "update dzzh_task_break_report set check_status='1',submit_user_id='"+submitUserId+"',submit_time=now(),submit_user_name='"+submitUserName+"' where dtbr_id='"+reportId+"'";
        jdbcTemplate.update(sql);
    }


    public void reportAudit(Report report, SUser sUser) {
        StringBuilder sql = new StringBuilder("update dzzh_task_break_report set check_status='"+report.getCheckStatus()+"'");
        if("2".equals(report.getCheckStatus())){
            sql.append(",reject_advice='").append(report.getRejectAdvice()).append("'");
        }
        sql.append(",reject_file='").append(PlatformObjectUtils.isNotEmpty(report.getRejectFile())?report.getRejectFile():"").append("'");
        sql.append(",reject_real_file='").append(PlatformObjectUtils.isNotEmpty(report.getRejectRealFile())?report.getRejectRealFile():"").append("'");
        sql.append(",audit_user='").append(sUser.getUserId()).append("'");
        sql.append(",audit_user_name='").append(sUser.getUserName()).append("'");
        sql.append(",audit_time=now()");
        sql.append(" where dtbr_id='").append(report.getReportId()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    public void uploadAttachment(String fileName,String realFileName, String reportId) {
    jdbcTemplate.update(
        "update dzzh_task_break_report set reject_file='"
            + fileName
            + "',reject_real_file='" + realFileName + "' where dtbr_id='"
            + reportId
            + "'");
    }
}
